package com.info33.platform.common.mvc.service.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.druid.pool.DruidDataSource;
import com.info33.platform.common.mvc.service.IJdbcService;

@Service
public class JdbcService implements IJdbcService {
	private static final String DELETE = "DELETE";
	private static final String SELECT = "SELECT";
	private static final String UPDATE = "UPDATE";
	private static final String INSERT = "INSERT";
	private static final String DROP = "DROP";
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	@Autowired
	DataSourceTransactionManager manager;
	/**
	 * 获取指定的jdbctemplate
	 * @param url
	 * @param userName
	 * @param password
	 * @param className
	 * @return
	 * @throws SQLException 
	 */
	public  JdbcTemplate getJdbcTemplate(String url,String userName,String password,String className) throws SQLException {
		DataSource dataSource = dataSource(url, userName, password, className);
		dataSource.getConnection().setAutoCommit(false);
		JdbcTemplate jdbc = new JdbcTemplate(dataSource);
		return jdbc;
	}
	public DataSourceTransactionManager   getTransaction() {
		return manager;
	}
	/**
	 * 获取项目默认的jdbctemplate
	 * @return
	 */
	@Override
	public JdbcTemplate getDefaultJdbcTemplate() {
		return jdbcTemplate;
	}
	/**
	 * 配置数据连接源
	 * @param url
	 * @param userName
	 * @param password
	 * @param className
	 * @return
	 */
	public DataSource dataSource(String url,String userName,String password,String className) {
		System.out.println(url);
		System.out.println(userName);
		System.out.println(className);
		//可以在此处调用相关接口获取数据库的配置信息进行 DataSource 的配置
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(className);
        return dataSource;
    }
	/**
	 * 查询单个对象
	 * @param jdbc
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	@Override
	public Map<String, Object> queryMap(String sql) throws Exception {
		checkSql(sql, SELECT, DELETE+"|"+DROP);
		return jdbcTemplate.queryForMap(sql);
	}
	/**
	 * 查询集合
	 * @param jdbc
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	@Override
	public List<Map<String,Object>> queryList(String sql) throws Exception{
		checkSql(sql, SELECT, DELETE+"|"+DROP);
		return jdbcTemplate.queryForList(sql);
	}
	/**
	 * 查询字符串
	 * @param jdbc
	 * @param sql
	 * @return
	 * @throws Exception 
	 */
	@Override
	public String queryString(String sql) throws Exception {
		checkSql(sql, SELECT, DELETE+"|"+DROP+"|"+UPDATE);
		return jdbcTemplate.queryForObject(sql, String.class);
	}
	@Override
	public Integer queryCount(String sql) throws Exception {
		checkSql(sql, SELECT, DELETE+"|"+DROP+"|"+UPDATE);
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}
	/**
	 * 更新
	 * @param jdbc
	 * @param sql
	 * @throws Exception 
	 */
	@Transactional(rollbackFor=Exception.class)
	@Override
	public int update(String sql) throws Exception {
		checkSql(sql, UPDATE, DELETE+"|"+DROP);
	    return jdbcTemplate.update(sql);
	            	
	}
	/**
	 * 删除
	 * @param jdbc
	 * @param sql
	 * @throws Exception 
	 */
	@Transactional(rollbackFor=Exception.class)
	@Override
	public int delete(String sql) throws Exception {
		checkSql(sql, DELETE, DROP+"|"+UPDATE);
	    return 	jdbcTemplate.update(sql);
	}
	@Transactional(rollbackFor=Exception.class)
	@Override
	public int insert(String sql) throws Exception {
		checkSql(sql, INSERT, DROP+"|"+UPDATE);
	    return 	jdbcTemplate.update(sql);
	}
	@Override
	public Long insertAndReturnId(String sql) {
		KeyHolder holder = new GeneratedKeyHolder();
		jdbcTemplate.update(connection -> {
			PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
			return ps;
		}, holder);
		Long realId = Objects.requireNonNull(holder.getKey()).longValue();
		return realId;
	}
	/**
	 * 校验SQL
	 * @param sql
	 * @param allow
	 * @param notallow
	 * @return
	 * @throws Exception 
	 */
	public static void checkSql(String sql,String allow,String notallow) throws Exception {
		String temp = sql.toUpperCase();
		Pattern yes = Pattern.compile(allow);
		Pattern not = Pattern.compile(notallow);
		Matcher y = yes.matcher(temp);
		Matcher n = not.matcher(temp);
		Boolean flag =  y.find()&&!n.find();
		if(!flag)
			throw new Exception("SQL语句不合法！");
	}
}
